package com.zx.inf.sys.service;

import com.zx.inf.sys.conf.DataSourceContextHolder;
import com.zx.inf.sys.dao.InfDsInfoMapper;
import com.zx.inf.sys.dao.InfServiceInfoMapper;
import com.zx.inf.sys.entity.InfDsInfo;
import com.zx.inf.sys.entity.InfServiceInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

@Service
public class QueryService {

    @Autowired
    private InfServiceInfoMapper infServiceInfoMapper;

    @Autowired
    private DataSourceService dataSourceService;

    @Autowired
    private InfDsInfoMapper infDsInfoMapper;

    public List<Map<String, Object>> executeQuery(String serviceName, Map<String, Object> params) {
        // 根据serviceName获取inf_service_info表中的记录
        InfServiceInfo serviceInfo = infServiceInfoMapper.getServiceInfoByName(serviceName);
        if (serviceInfo == null) {
            throw new RuntimeException("Service not found");
        }

        // 获取对应的数据源信息
        InfDsInfo dsInfo = infDsInfoMapper.getDsInfoById(serviceInfo.getDsId());
        if (dsInfo == null) {
            throw new RuntimeException("DataSource not found");
        }

        // 设置当前线程的数据源类型
        DataSourceContextHolder.setDataSourceType(serviceInfo.getDsId().toString());

        // 解析并替换SQL模板中的占位符
        String sqlTemplate = serviceInfo.getInfSql();
        String sql = parseSqlTemplate(sqlTemplate, params);

        // 执行SQL并返回结果
        DataSource dataSource = DataSourceContextHolder.getDataSource(serviceInfo.getDsId().toString());
        try (Connection conn = dataSource.getConnection()) {
            if (dsInfo.getDsType() == 1) { // MySQL
                return executeMySqlQuery(conn, sql, params);
            } else if (dsInfo.getDsType() == 2) { // ClickHouse
                return executeClickHouseQuery(conn, sql, params);
            } else {
                throw new RuntimeException("Unsupported data source type");
            }
        } catch (SQLException e) {
            throw new RuntimeException("Failed to execute query", e);
        }
    }

    private String parseSqlTemplate(String sqlTemplate, Map<String, Object> params) {
        for (Map.Entry<String, Object> entry : params.entrySet()) {
            String key = entry.getKey();
            Object value = entry.getValue();
            if(value instanceof String){
                sqlTemplate = sqlTemplate.replace("#{" + key + "}", "'" +value + "'");
            }else {
                sqlTemplate = sqlTemplate.replace("#{" + key + "}", value + "");
            }
        }
        return sqlTemplate;
    }

    private List<Map<String, Object>> executeMySqlQuery(Connection conn, String sql, Map<String, Object> params) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        return resultSetToMap(rs);
    }

    private List<Map<String, Object>> executeClickHouseQuery(Connection conn, String sql, Map<String, Object> params) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();
        return resultSetToMap(rs);
    }

    private List<Map<String, Object>> resultSetToMap(ResultSet rs) throws SQLException {
        List<Map<String, Object>> result = new ArrayList<>();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (rs.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                row.put(metaData.getColumnName(i), rs.getObject(i));
            }
            result.add(row);
        }
        return result;
    }
}
